package com.troyhigh.njrotc.admintracker;

import java.util.ArrayList;
import java.util.Hashtable;
import java.sql.*;

//This is a permanently ArrayList way. No real-time modifications. All cached.

/**
 * A class that implements the Database interface in a way that uses SQL.
 * 
 * @author Quan Nguyen
 * @version 2008-December-23
 */
public class SQLDatabase implements Database
{
    private ArrayList<Cadet> cadets;
    private ArrayList<Event> events;
    
    private Connection connection;
    private String user;
    private String host;
    private String pass;
    private boolean connected = false;
    
    private final String DATABASE = "admintrackertwo";
    private final String EVENTSTABLE = "eventsnew";
    private final String CADETSTABLE = "students";
    private final String RIBBONSTABLE = "ribbons";
    private final String SEMESTERTABLE = "semester";
    
    private boolean autocommit = true;
    
    public void connect()
    {
        try
        {
            System.out.println("[sql] Attempting connection...");
            java.util.Scanner scanner = new java.util.Scanner(System.in);
            System.out.print  ("[sql] Credentials required. Username?> ");
            user = scanner.nextLine();
            System.out.print  ("[sql] Host? (Computer)> ");
            host = scanner.nextLine();
            System.out.print  ("[sql] Password?> ");
            pass = scanner.nextLine();
            System.out.println("[sql] Credential entry complete.");
            Class.forName("com.mysql.jdbc.Driver").newInstance(); 
            //scanner.close(); //This seems to make ATCL freak out for some reason.
            connection = DriverManager.getConnection("jdbc:mysql://"+host+"/test?" 
                    + "user="+user+"&password="+pass); 
            Statement use = connection.createStatement();
            System.out.println("[sql] Connection established.");
            System.out.println("[sql] You are connected as " + user + "@" + host + ".");
            use.execute("USE " + DATABASE);
            connected = true;
        }
        catch (SQLException e)
        {
            System.out.println("[sql error] SQL Exception: SQLDatabase failed to connect.");
            System.out.println("[sql error] See if the MySQL service is active.");
            e.printStackTrace();
            System.out.println("[sql error] AdminTracker can no longer continue.");
            System.exit(1);
        }
        catch (ClassNotFoundException e)
        {
            System.out.println("[jdbc error] JDBC Driver not found!");
            e.printStackTrace();
            System.out.println("[sql error] AdminTracker can no longer continue.");
            System.exit(1);
        }
        catch (Exception e)
        {
            System.out.println("[sql error] General Exception has occurred.");
            e.printStackTrace();
            System.out.println("[sql error] AdminTracker can no longer continue.");
            System.exit(1);
        }
        init();
    }
    
    private void init()
    {
        loadCadets();
        System.out.println("[sql init] Initialized Cadet database.");
        loadEvents();
        System.out.println("[sql init] initialized Event database.");
    }
    
    private void loadCadets()
    {
        if(connected)
        {
            ArrayList<Cadet> c = new ArrayList<Cadet>();
            try
            {
                Statement getcadets = connection.createStatement();
                ResultSet cadetSet = getcadets.executeQuery("SELECT * FROM " + CADETSTABLE + " ORDER BY lastName");
                cadets = buildCadets(cadetSet);
            }
            catch (SQLException sqle)
            {
                System.out.println("[sql error] SQL Exception: Loading the students failed.");
                sqle.printStackTrace();
            }
        }
    }
    
    private ArrayList<Cadet> buildCadets(ResultSet set)
    {
        ArrayList<Cadet> result = new ArrayList<Cadet>();
        if(set == null) return result;
        try
        {
            set.first();
            if(set.isAfterLast()) return result;
            //I don't like using magic numbers, but I doubt that these fields
            //will ever change, so in the case of that I'll just change them.
            do
            {
                int pla = set.getInt(1);
                String las = set.getString(2);
                String fir = set.getString(3);
                int id = set.getInt(4);     
                char gen = set.getString(5).charAt(0);
                int gra = set.getInt(6);
                int nsy = set.getInt(7);
                int[] rib = getRibbons(id);
                int[] sem = getSemester(id);
                result.add(new Cadet(pla, las, fir, id, gen, gra, nsy, rib, sem));
                set.next();
            } while (!set.isAfterLast());
            return result;
        }
        catch (SQLException sqle)
        {
            System.out.println("[sql error] SQL Exception: Getting the students failed.");
            sqle.printStackTrace();
        }
        return new ArrayList<Cadet>();
    }
    
    private int[] getRibbons(int id)
    {
        if(connected)
        {
            String query = "SELECT * FROM " + RIBBONSTABLE;
            if(id > 0)
            {
                query += " WHERE ";
            }
            else return null;
            query += "id = " + id;
            try
            {
                Statement getAwardSM = connection.createStatement();
                ResultSet rs = getAwardSM.executeQuery(query);
                /*if(rs.isAfterLast())
                return null;
                //Proceed to creating an array for the data. Only does first one.
                rs.first();*/
                if(!rs.first()) return null;
                int[] arr = new int[25];
                for(int i = 1; i <= 25; i++)
                {
                    arr[i-1] = rs.getInt(i+1);
                }
                return arr;
            }
            catch(SQLException sqle)
            {
                System.out.println("[sql error] SQL Exception: Getting the ribbons failed.");
                sqle.printStackTrace();
            }
        }
        return null;
    }
    
    private int[] getSemester(int id)
    {
        if(connected)
        {
            String query = "SELECT * FROM " + SEMESTERTABLE;
            if(id > 0)
            {
                query += " WHERE ";
            }
            else return null;
            query += "id = " + id;
            try
            {
                Statement getAwardSM = connection.createStatement();
                ResultSet rs = getAwardSM.executeQuery(query);
                if(rs.isAfterLast())
                    return null;
                //Proceed to creating an array for the data. Only does first one.
                rs.first();
                int[] arr = new int[25];
                for(int i = 1; i <= 25; i++)
                {
                    arr[i-1] = rs.getInt(i+1);
                }
                return arr;
            }
            catch(SQLException sqle)
            {
                System.out.println("[sql error] SQL Exception: Getting the ribbons failed.");
                sqle.printStackTrace();
            }
        }
        return null;
    }
        
    private void loadEvents()
    {
        if(connected)
        {
            ArrayList<Event> e = new ArrayList<Event>();
            try
            {
                Statement getEventsSM = connection.createStatement();
                ResultSet eventSet = getEventsSM.executeQuery("SELECT * FROM " + EVENTSTABLE + " ORDER BY started");
                events = buildEvent(eventSet);
            }
            catch (SQLException sqle)
            {
                System.out.println("[sql error] SQL Exception: Events failed to load.");
                sqle.printStackTrace();
            }
        }
    }
    
    private ArrayList<Event> buildEvent(ResultSet set)
    {
        ArrayList<Event> result = new ArrayList<Event>();
        if(set == null) return result;
        try
        {
            set.first();
            if(set.isAfterLast()) return null;
            do
            {
                String nam = set.getString(1);
                Date dat = set.getDate(2);
                Category cat = Category.valueOf(set.getString(3));
                //Counting was the original value here. It is obsolete.
                int dur = set.getInt(5);
                String id = AdminTracker.rosterID(nam, dat);
                Hashtable<Integer, Integer> tab = getTable(id);
                result.add(new Event(nam, dat, cat, dur, tab));
                set.next();
            } while(!set.isAfterLast());
            return result;
        }
        catch (SQLException sqle)
        {
            System.out.println("[sql error] SQL Exception: Building events failed.");
            sqle.printStackTrace();
        }
        return new ArrayList<Event>();
    }
    
    private Hashtable<Integer, Integer> getTable(String id)
    {
        if(connected)
        {
             if(id == null || id.equals("")) return null;
             try
             {
                if(!rosterExists(id)) //if the event doesn't have an entry...
                {
                    Statement nt = connection.createStatement();
                    nt.execute("CREATE TABLE " + id + " LIKE roster");
                    return new Hashtable<Integer, Integer>();
                }
                Statement getRoster = connection.createStatement();
                ResultSet rs = getRoster.executeQuery("SELECT * FROM " + id);
                Hashtable<Integer, Integer> roster = new Hashtable<Integer, Integer>();
                if(rs.first())
                {
                    while(!rs.isAfterLast())
                    {
                        roster.put(new Integer(rs.getInt(1)), new Integer(rs.getInt(2)));
                        rs.next();
                    }
                }
                return roster;
             }
             catch (SQLException sqle)
             {
                 System.out.println("[sql error] SQL Exception: Getting the table failed.");
                 sqle.printStackTrace();
             }
        }
        return new Hashtable<Integer, Integer>();
    }
    
    private boolean rosterExists(String id)
    {
        if(connected)
        {
            if(id == null || id.equals("")) return false;
            try
            {
                Statement tbls = connection.createStatement();
                ResultSet rs = tbls.executeQuery("SHOW TABLES WHERE TABLES_IN_"+DATABASE.toUpperCase()+ " = \"" + id +"\"");
                return rs.first();
            }
            catch (SQLException sqle)
            {
                System.out.println("[sql error] SQL Exception: Failed to determine if roster exists.");
                sqle.printStackTrace();
            }
        }
        return false;
    }
    
    public boolean isConnected() { return connected; }
    
    public void commit()
    {
        saveCadets();
        System.out.println("[sql save] Saved cadet database.");
        saveEvents();
        System.out.println("[sql save] Saved event database.");
    }
    
    private void saveCadets()
    {
        if(connected)
        {
            try
            {
                connection.createStatement().execute("DELETE FROM " + CADETSTABLE);
                for(Cadet c : cadets)
                {
                    if(connected && c.getID() > 0)
                    {
                        String columnValues = "(platoon,lastname,firstname,id,gender,grade,nsyear)";
                        String studentValues = formStudentValues(c);
                        String insert = "REPLACE INTO " + CADETSTABLE + " " + columnValues +
                                        " VALUES"+studentValues;
                        Statement insertStudent = connection.createStatement();
                        insertStudent.execute(insert);     
                        setRibbons(c);
                        setSemester(c);
                    }
                }
            }
            catch (SQLException e)
            {
                System.out.println("[sql error] SQL Exception! Saving the cadets failed.");
                e.printStackTrace();
            }
        }
    }
    
    private String formStudentValues(Cadet c)
    {
        return "("+c.getPlatoon()+",'"+c.getLastName()+"','"+c.getFirstName()+
               "',"+c.getID()+",'"+c.getGender()+"',"+c.getGrade()+","+c.getNSYear()+")";
    }
    
    private void setRibbons(Cadet c)
    {
        if(connected && c != null)
        {
            try
            {
                String columnValues = AdminTracker.RIBBON_LABELS;
                String awardValues = c.ribbonValues();
                String insert = "REPLACE INTO " + RIBBONSTABLE + " (id, " + columnValues + ")" +
                                " VALUES("+ c.getID() + ", " +awardValues+")";
                Statement insertAward = connection.createStatement();
                insertAward.execute(insert);
            }
            catch(SQLException se)
            {
                System.out.println("[sql error] SQL Exception: Setting ribbons failed.");
                se.printStackTrace();
            }
        }      
    }
    
    private void setSemester(Cadet c)
    {
        if(connected && c != null)
        {
            try
            {
                String columnValues = AdminTracker.RIBBON_LABELS;
                String awardValues = c.semesterValues();
                String insert = "REPLACE INTO " + SEMESTERTABLE + " (id, " + columnValues + ")" +
                                " VALUES("+ c.getID() + ", " +awardValues+")";
                Statement insertAward = connection.createStatement();
                insertAward.execute(insert);
            }
            catch(SQLException se)
            {
                System.out.println("[sql error] SQL Exception: Setting ribbons failed.");
                se.printStackTrace();
            }
        }    
    }
    
    private void saveEvents()
    {
        if(connected)
        {
            try
            {
                connection.createStatement().execute("DELETE FROM " + EVENTSTABLE);
                for(Event e : events)
                {
                    if(!e.getName().equals("") && e.getDate() != null)
                    {
                        String columnValues = "(name, started, type, counting, countingvalue)";
                        String eventValues = formEventValues(e);
                        String insert = "REPLACE INTO " + EVENTSTABLE + " " + columnValues +
                                        " VALUES"+eventValues;
                        Statement insertEvent = connection.createStatement();
                        insertEvent.execute(insert);
                        setRoster(e);
                    }
                }
            }
            catch(SQLException se)
            {
                System.out.println("[sql error] SQL Exception! Saving events failed.");
                se.printStackTrace();
            }
        }
    }
    
    private String formEventValues(Event e)
    {
        String name = e.getName();
        String date = e.getDate().toString();
        String cate = e.getCategory().toString();
        int coun = e.getDuration();
        String valu = (e.getCategory().equals(Category.US) ||
                       e.getCategory().equals(Category.CS) ? "HOURS" : "ATTENDED");
        return "('"+name+"', '"+date+"', '"+cate+"', '"+valu+"', " + coun+")";
    }
    
    private void setRoster(Event e)
    {
        if(connected)
        {
            try
            {
                Statement drop = connection.createStatement();
                if(rosterExists(e.getRosterID())) drop.execute("DROP TABLE " + e.getRosterID());
                drop.execute("CREATE TABLE " + e.getRosterID() + " LIKE roster");
                
                for(Integer i : e.getIDs())
                {
                    Statement insert = connection.createStatement();
                    String state = "INSERT INTO " + e.getRosterID() + " (id, value)"+
                                   " VALUES(" + i + ", " + e.get(i) + ")";
                    insert.execute(state);
                }
            }
            catch (SQLException sqle)
            {
                System.out.println("[sql error] SQL Exception: Setting the roster failed.");
                sqle.printStackTrace();
            }
        }
    }
    
    public void disconnect()
    {
        try
        {
            System.out.println("[sql term] Closing connection...");
            if(!connection.isClosed())
            {
                if(!connection.getAutoCommit()) connection.commit();
                connection.close();
            }
            System.out.println("[sql term] Connection successfully closed.");
            connected = false;
        }
        catch (SQLException e)
        {
            System.out.println("[sql error] SQL Exception: Connection failed to close.");
            e.printStackTrace();
        }
        catch (Exception e)
        {
            System.out.println("[sql error] General Exception in closing connection.");
            e.printStackTrace();
        } 
    }
    
    public boolean canBackup()
    {
        return false; //SQL does not support backup operations as far as I know.
    }
    
    public void backup()
    {
        System.out.println("[sql error] SQLDatabase does not support backup operations.");
    }
    
    public void restore(String filename)
    {
        System.out.println("[sql error] SQLDatabase does not support restore operations.");
    }
    
    public boolean isAutoCommit() { return autocommit; }  
    public void setAutoCommit(boolean auto) { autocommit = auto; }
    
    public ArrayList<Cadet> getCadets() { return cadets; }
    public ArrayList<Event> getEvents() { return events; } 
    public void setCadets(ArrayList<Cadet> array)
    { 
        if(array != null) cadets = array; 
        if(autocommit)
        {
            System.out.println("[sql commit] Auto-committing...");
            commit();
        }
    } 
    public void setEvents(ArrayList<Event> array)
    { 
        if(array != null) events = array; 
        if(autocommit)
        {
            System.out.println("[sql commit] Auto-committing...");
            commit();
        }
    }
} 